/********************************************************************************
 * Project Name		[JavaEE_JDBC]
 * File Name     	[TestTransaction.java]
 * Creation Date 	[2015-01-01]
 * 
 * Copyright© ge.y.yang@gmail.com All Rights Reserved
 * 
 * Work hard, play harder, think big and keep fit
 ********************************************************************************/
package jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import utils.jdbc.JdbcUtils;

/**
 * JDBC事务测试
 * 
 * @author 不落的太阳(Sean Yang aka ShortPeace)
 * @version 1.0
 * @since jdk 1.8
 * 
 */
public class TestTransaction {

	private Connection connection;
	private PreparedStatement statement;
	private ResultSet resultSet;

	@Before
	public void init() throws Exception {
		connection = JdbcUtils.getInstance().getConnection();
	}

	/**
	 * 数据库事务的完整实例
	 */
	@Test
	public void testTransaction() {
		String sql = "UPDATE User u SET username = ? WHERE id = ?";

		Savepoint savepoint = null;
		try {
			// 关闭自动提交事务
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(sql);
			statement.setString(1, "ShortPeace");
			statement.setInt(2, 5);

			// 创建回滚点
			savepoint = connection.setSavepoint();

			// 执行操作
			statement.executeUpdate();

			// 提交事务
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			// 回滚事务
			try {
				connection.rollback(savepoint);
			} catch (SQLException e1) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 测试数据库隔离级别<br/>
	 * 
	 * 测试方法:<br/>
	 * 1. dao.impl1.UserDaoImpl的addUser()方法的注释去掉, 在connection.commit()行加入断点<br/>
	 * 2. 以debug的方式运行UserDaoImplTest_1的testAddUser()方法<br/>
	 * 3. 运行本方法<br/>
	 * 
	 * 结论<br/>
	 * 由于数据库隔离级别被设置为了READ_UNCOMMITTED, 因此添加的用户虽未提交, 但是已经可以被读到<br/>
	 */
	@Test
	public void testTransactionIsolation() {
		try {
			// MySQL中查询事务级别SELECT @@tx_isolation;

			// 设置数据库事务的隔离级别
			connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
			String sql = "SELECT count(*) FROM User";
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				System.out.println(resultSet.getObject(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@After
	public void destroy() {
		JdbcUtils.getInstance().close(resultSet, statement, connection);
	}
}
